package com.no1.musicmgr.mapper;


import com.no1.musicmgr.vo.UserVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Mapper
public interface UserAnalysisMapper {

    // 性别比例图--性别人数
    @Select("select user_sex,count(*) as count from user group by user_sex")
    ArrayList<UserVo> getUserDataBySex();

    // 用户人数
    @Select("select user_sf,count(*) as count from user where user_sf = 1 group by user_sf")
    ArrayList<UserVo> getPersonData();

    // 用户上次登录的时间
    @Select("SELECT MONTH(loginDate) AS month, COUNT(*) AS personActive FROM user " +
            "WHERE YEAR(loginDate) = YEAR(CURRENT_DATE) AND user_sf = 1" +
            " GROUP BY MONTH(loginDate)")
    ArrayList<UserVo> getLoginCountPerMonth();

    @Select("SELECT COUNT(*) AS totalActivePerson " +
            "FROM user " +
            "WHERE YEAR(loginDate) = YEAR(CURRENT_DATE) " +
            "AND user_sf = 1")
    Integer getTotalActivePersonInYearForStatusOne();

    @Select("SELECT COUNT(*) AS totalActivePerson " +
            "FROM user " +
            "WHERE YEAR(loginDate) = YEAR(CURRENT_DATE) - 1 " +
            "AND user_sf = 1")
    Integer getTotalActivePersonInLastYearForStatusOne();

}
